package com.xjgc.ws.eip.dao;
/*
 * 
 * EIP生产订单接口
 * 
 */

import com.xjgc.common.config.DbConn;
import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.joda.time.DateTimeUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Component
public class ZxjEip2cAufnr {
	@Autowired
	DbConn db;

	public String aufnr(String xmlData) throws SQLException {
		// TODO Auto-generated method stub
		String rs = null;
		PreparedStatement inPst = null;
		PreparedStatement uptPst = null;
		
		// 定义执行SQL脚本
		String inSql = "INSERT INTO zxj_eip_2caufnr(no,salesorderno,salesorderlineprojectsno,materialno,specificationtype,"
				+ "demandquantity,plannedstartuptime,plannedendtime,actualstarttime,actualendtime,status,productionstatus,"
				+ "tags,procedures,labels,ioflag,bstkd,mstatus,requiredDate,unit) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; // 20插入SQL
		
		String uptSql = "update zxj_eip_2caufnr set salesorderno=?,salesorderlineprojectsno=?,materialno=?,specificationtype=?,"
				+ "demandquantity=?,plannedstartuptime=?,plannedendtime=?,actualstarttime=?,actualendtime=?,status=?,productionstatus=?,"
				+ "tags=?,procedures=?,labels=?,ioflag=?,bstkd=?,mstatus=?,unit=? where no=?"; // 18插入SQL
		
		// 创建数据库连接
		inPst = db.getConnetion().prepareStatement(inSql);
		uptPst = db.getConnetion().prepareStatement(uptSql);
		int count = 0;
//		System.out.println("******xmlData******" + xmlData.toString());
		// 解析xml文件
		Document doc = null;
		String eip1 = ""; // 上一条eip关键字段组合数据
		try {
			doc = DocumentHelper.parseText(xmlData);
			Element root = doc.getRootElement();
			Iterator<?> it = root.elementIterator();
			while (it.hasNext()) {
				Element element = (Element) it.next();

				String aufnr = element.elementText("AUFNR");
				String kdauf = element.elementText("KDAUF");//本单位销售单号

				String kdpos = element.elementText("KDPOS");
				String matnr = element.elementText("MATNR");
				String maktx = element.elementText("MAKTX").replace("【禁用】", "");
				String bstkd = element.elementText("BSTKD");//销售订单合同号
				String werks = element.elementText("WERKS");//工厂
				String s_gamng = element.elementText("GAMNG");
				String zpinghao = element.elementText("ZPINGHAO");//屏号
				String zkdauf = StringUtils.substringBefore(zpinghao, "-");//最上级单位 销售单号
				int i = s_gamng.lastIndexOf(".");
				s_gamng = s_gamng.substring(0, i);
				int gamng = Integer.valueOf(s_gamng);
				String s_gstrp = element.elementText("GSTRP");
				if(s_gstrp.equals("00000000")) {
					s_gstrp ="19000101";
				}
				Timestamp gstrp = StringTotimeStamp(s_gstrp);
				String s_gltrp = element.elementText("GLTRP");
				if(s_gltrp.equals("00000000")) {
					s_gltrp ="19000101";
				}
				Timestamp gltrp = StringTotimeStamp(s_gltrp);
				String s_ftrmi = element.elementText("FTRMI");
				if(s_ftrmi.equals("00000000")) {
					s_ftrmi ="19000101";
				}
				Timestamp ftrmi = StringTotimeStamp(s_ftrmi);
				String s_gltri = element.elementText("GLTRI");
				if(s_gltri.equals("00000000")) {
					s_gltri ="19000101";
				}
				Timestamp gltri = StringTotimeStamp(s_gltri);
				String loekz = element.elementText("LOEKZ");
				String txt04 = element.elementText("TXT04");
				String gongxu = "";
				String unit = element.elementText("GMEIN");

				if(werks.equals("5033")) {
					Pattern pzz = Pattern.compile("装置");
					Matcher mzz = pzz.matcher(maktx);
					while(mzz.find()) {
						gongxu = "{\"10\": { \"Id\": null, \"No\": null,   \"Name\": null, \"Labels\": null, \"Remark\": null, \"TenantId\": null, \"Parameters\": null, \"RowVersion\": null}, \"20\": { \"Id\": null, \"No\": null, \"Name\": null, \"Labels\": null, \"Remark\": null,\"TenantId\": null, \"Parameters\": null, \"RowVersion\": null}}";
					}
					Pattern pxt = Pattern.compile("箱体");
					Matcher mxt = pxt.matcher(maktx);
					while(mxt.find()) {
						gongxu = "{\"10\": { \"Id\": null, \"No\": null,   \"Name\": null, \"Labels\": null, \"Remark\": null, \"TenantId\": null, \"Parameters\": null, \"RowVersion\": null}, \"20\": { \"Id\": null, \"No\": null, \"Name\": null, \"Labels\": null, \"Remark\": null,\"TenantId\": null, \"Parameters\": null, \"RowVersion\": null}}";
					}
				}else if(werks.equals("5036")) {
					gongxu = "";
				}
				Timestamp reqDate = StringTotimeStamp("19000101");

				String eip2 = aufnr + kdauf + matnr + maktx + gamng + gstrp + gltrp + ftrmi + gltri + loekz + txt04;// 将eip所有必填字段都加上
				if (eip1 == eip2||eip1.equals(eip2)) {// 比对上下两条不一致，执行插入判断逻辑
				}else {
					if (isSGxm(bstkd)) {// 根据销售合同号 是否为国网订单
						if (queryData(aufnr)) {// true 表中无此数据需要插入

							inPst.setString(1, aufnr);// no 生产订单号 y
							inPst.setString(2, zkdauf);// salesorderno 销售订单号 y ********（最上级销售单号）
							inPst.setString(3, kdpos);//  salesorderlineprojectsno  销售订单行项目
							inPst.setString(4, matnr);// materialno 物料编号 y
							inPst.setString(5, maktx);// specificationtype 物料描述字段 y
							inPst.setInt(6, gamng);// demandquantity 需求数量 y
							inPst.setTimestamp(7, gstrp);// plannedstartuptime 计划开始时间 y
							inPst.setTimestamp(8, gltrp);// plannedendtime 计划结束时间 y
							inPst.setTimestamp(9, ftrmi);// actualstarttime 实际开始时间 y
							inPst.setTimestamp(10, gltri);// actualendtime 实际结束时间 y
							if(loekz == null ||loekz.equals("")) {
								loekz = "1";
							}else if(loekz.equals("X")) {
								loekz = "2";
							}
							inPst.setString(11, loekz);// status 订单状态 y
							if(loekz.equals("1")) {
								inPst.setString(12, getProductionStatus(txt04));// productionstatus 生产状态 y
							}else {
								inPst.setString(12, "X");// leokz 为2 表示删除，此数据不传给国网
							}

							inPst.setString(13, getBstkd(bstkd));// tags 需要同步的数据中心 客户所属省份 ***需截取***
							inPst.setString(14, gongxu);// procedures 工序
							inPst.setString(15, "");// labels 备用字段
							inPst.setString(16, "1");// ioflag 代理服务读取状态 0:已读 1:未读
							inPst.setString(17, bstkd);//
							inPst.setString(18, "1");// 新增修改标志
							inPst.setTimestamp(19, reqDate);// 新增修改标志
							inPst.setString(20, getUnit(unit));// 计量单位

							// 插入更新数据
							int row = inPst.executeUpdate();
							if (row > 0) {
								count++;
							}
						}else {

							uptPst.setString(1, zkdauf);// salesorderno 销售订单号 y ********（最上级销售单号）
							uptPst.setString(2, kdpos);//  salesorderlineprojectsno  销售订单行项目
							uptPst.setString(3, matnr);// materialno 物料编号 y
							uptPst.setString(4, maktx);// specificationtype 物料描述字段 y
							uptPst.setInt(5, gamng);// demandquantity 需求数量 y
							uptPst.setTimestamp(6, gstrp);// plannedstartuptime 计划开始时间 y
							uptPst.setTimestamp(7, gltrp);// plannedendtime 计划结束时间 y
							uptPst.setTimestamp(8, ftrmi);// actualstarttime 实际开始时间 y
							uptPst.setTimestamp(9, gltri);// actualendtime 实际结束时间 y
							if(loekz == null ||loekz.equals("")) {
								loekz = "1";
							}else if(loekz.equals("X")) {
								loekz = "2";
							}
							uptPst.setString(10, loekz);// status 订单状态 y
							if(loekz.equals("1")) {
								uptPst.setString(11, getProductionStatus(txt04));// productionstatus 生产状态 y
							}else {
								uptPst.setString(11, "X");// leokz 为2 表示删除，此数据不传给国网
							}

							uptPst.setString(12, getBstkd(bstkd));// tags 需要同步的数据中心 客户所属省份 ***需截取***
							uptPst.setString(13, gongxu);// procedures 工序
							uptPst.setString(14, "");// labels 备用字段
							uptPst.setString(15, "1");// ioflag 代理服务读取状态 0:已读 1:未读
							uptPst.setString(16, bstkd);//
							uptPst.setString(17, "2");// 新增修改标志
							uptPst.setString(18, aufnr);// no 生产订单号 y
							uptPst.setString(19, getUnit(unit));//计量单位
							System.out.println("******************  batkd==="+bstkd +"   aufnr="+aufnr);
							// 插入更新数据
							int row = uptPst.executeUpdate();
							if (row > 0) {
								count++;
							}
						}
					}
					eip1 = eip2;
				}
			}
			if (count > 0) {
				rs = "0";// 执行成功
			}
		} catch (DocumentException e1) {
			rs = e1.toString();// 执行失败
			e1.printStackTrace();
		}finally {
			// 关闭连接
			inPst.close();
			db.closeCn();
		}

		return rs;
	}
	/*
	 * 查询EIP 生产订单表 zxjeip2caufnr是否存在 传入参数字段均不能为空
	 */
	public boolean queryData(String aufnr) throws SQLException {
		boolean bl = true;

		String sql = "select count(*) \"counts\" from zxj_eip_2caufnr where no='" + aufnr + "'";
		ResultSet rs = null;
		PreparedStatement pst = null;
		pst = db.getConnetion().prepareStatement(sql);
		rs = pst.executeQuery();
		while (rs.next()) {
			if (rs.getInt("counts") > 0) {
				bl = false;
			}
		}
		pst.close();
		db.closeCn();
		return bl;
	}

	/*
	 * 查找销售订单合同号 判定是否为国网项目
	 */
	public boolean isSGxm(String bstkd) throws SQLException {
		boolean bl = false;
		if (bstkd == null || bstkd.equals("")) {
			bl = false;
		} else {
			String sql = "select count(*) \"counts\" from zxj_eip_2cvbeln where bstkd='" + bstkd + "'";
			ResultSet rs = null;
			PreparedStatement pst = null;
			pst = db.getConnetion().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				if (rs.getInt("counts") > 0) {
					bl = true;
				}
			}
			pst.close();
			db.closeCn();
		}
		return bl;
	}

	/*
	 * 根据销售合同号 获取采购订单省份
	 */
	public String getBstkd(String bstkd) throws SQLException {
		String its = "";
		if (bstkd != null||!bstkd.equals("")) {
			String sql = "select distinct tags from zxj_eip_2cvbeln where bstkd='" + bstkd + "'";
			ResultSet rs = null;
			PreparedStatement pst = null;
			pst = db.getConnetion().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				its = rs.getString("tags");
			}
			pst.close();
			db.closeCn();
		}
		return its;
	}

	/*
	 * 根据计量单位编码返回单位名称
	 */
	public String getUnit(String unitId) throws SQLException {
		String its = "";
		if (unitId != null||!unitId.equals("")) {
			String sql = "select \"MSEHL\" from \"T006\" t where t.\"MSEHI\" = '" + unitId + "'";

			ResultSet rs = null;
			PreparedStatement pst = null;
			pst = db.getConnetion().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				its = rs.getString("MSEHL");
			}
			pst.close();
			db.closeCn();
		}
		return its;
	}
	

	
	/*
	 * 根据生产状态 
	 * 1   ===  非REL  包含 CRTD
	 * 2   ===  非 DLV  非TECO 包含 REL
	 * 4   ===  非PDLV 包含DLV
	 * 
	 */
	public String getProductionStatus(String txt04) throws SQLException {
		String pStatus = "X";
		
		boolean DLV = StringUtils.contains(txt04, "DLV");
		boolean PDLV = StringUtils.contains(txt04, "PDLV");
		boolean TECO = StringUtils.contains(txt04, "TECO");
		boolean REL = StringUtils.contains(txt04, "REL");
		boolean CRTD = StringUtils.contains(txt04, "CRTD");
		
		//非PDLV 包含DLV
		if(!PDLV&&DLV){
			pStatus="4";
		}
		
		if(!DLV&&!TECO&&REL||PDLV&&!TECO&&REL) {
			pStatus="2";
		}
		
		if(!REL&&CRTD) {
			pStatus="1";
		}
		return pStatus;
	}

	/*
	 * String转换Timestamp
	 */
	public  Timestamp StringTotimeStamp(String dateString) {
		if (dateString.length() < 10) {
			dateString = dateString.substring(0, 4) + "-" + dateString.substring(4, 6) + "-"
					+ dateString.substring(6, 8);
		}
		StringBuffer ds = new StringBuffer(dateString);
		if (dateString.length() > 10) {
			Timestamp ts = Timestamp.valueOf(dateString);
			return ts;
		} else {
			ds.append(" 00:00:00");
			Timestamp ts = Timestamp.valueOf(ds.toString());
			return ts;
		}
	}
	
}
